import click
from flask import current_app
from .dal import db
from .model import User,Site
from flask.cli import with_appcontext
stus = [
    {"id":"2021313031","sid":"5","name":"崔世杰","qq":"1215491002","autocard":True},
    {"id":"2020351024","sid":"1","name":"孟嘉琪","qq":"1063580506","autocard":False},
    {"id":"2021313054","sid":"1","name":"何璟莹","qq":"1165566799","autocard":False},
    {"id":"2021313039","sid":"4","name":"涂韩韩","qq":"1007057952","autocard":True},
    {"id":"2018102927","sid":"6","name":"狐狸","qq":"2644582804","autocard":True},
    {"id":"2020351025","sid":"1","name":"叶兴娅","qq":"","autocard":False},
    {"id":"2019100040","sid":"1","name":"苏喆义","qq":"1063580506","autocard":False}
    ]
sites = {
    "9栋":{"site":[116.042146,28.695328],"province":"江西省","city":"南昌市","district":"南昌县",'street':"天祥大道289号南昌工程学院"},
    "14B118":{"site":[116.042254,28.697625],"province":"江西省","city":"南昌市","district":"南昌县",'street':"天祥大道289号南昌工程学院"},
    "机电楼215":{"site":[116.033702,28.696944],"province":"江西省","city":"南昌市","district":"南昌县",'street':"天祥大道289号南昌工程学院"},
    "新力怡园":{"site":[115.817603,28.703819],"province":"江西省","city":"南昌市","district":"新建区",'street':"幸福北路"},
    "太阳石":{"site":[113.803522,35.487768],"province":"河南省","city":"新乡市","district":"辉县市",'street':"太阳石小区"},
    "狐狸家":{"site":[114.95726,27.823796],"province":"江西省","city":"新余市","district":"渝水区",'street':"中大·水岸丽都"}
       }

"""-------------------------insert-data------------------------------"""

# 插入用户信息
def insertStu():
    for item in stus:
        # 实例化对象
        mod = User(item['id'],item['sid'],item['name'],item['qq'],item['autocard'],False)
        sql = f"""insert into userTb (id,sid,name,qq,autocard,sendEmail) values('{mod.id}','{mod.sid}','{mod.name}','{mod.qq}','{1 if mod.autocard else 0}','{1 if mod.sendEmail else 0}')"""
        db.goSQL(sql)

# 插入位置信息
def insertSite():
    for item in sites:
        mod = Site(None,item,sites[item]['site'],sites[item]['province'],sites[item]['city'],sites[item]['district'],sites[item]['street'],"developer")
        sql = f"""insert into siteTb (name,site,province,city,district,street,creater) values('{mod.name}','{mod.site}','{mod.province}','{mod.city}','{mod.district}','{mod.street}','{mod.creater}')"""
        db.goSQL(sql)

# 定义一个名为 insert-data 命令行
@click.command('insert-data')
@with_appcontext
def insert_data_command():
    insertStu()
    insertSite()
    # 输出日志
    click.echo('Insert the data success.')

"""-------------------------init-db------------------------------"""

# 初始化数据库文件，创建表
def init_db():
    # 读取文件内容，执行文件
    with current_app.open_resource('autocard.sql') as f:
        db.executescript(f.read().decode('utf8'))

# 定义一个名为 init-db 命令行
@click.command('init-db')
@with_appcontext
def init_db_command():
    # 清除之前的数据，创建表
    """Clear the existing data and create new tables."""
    init_db()
    # 输出日志
    click.echo('Initialized the database.')

# 在函数中进行注册
def init_app(app):
    # 返回响应后进行清理的时候调用此函数
    app.teardown_appcontext(db.close_db)
    # 添加一个新的 可以与 flask 一起工作的命令
    app.cli.add_command(init_db_command)
    app.cli.add_command(insert_data_command)

"""-------------------------clear-site------------------------------"""

# 定义一个名为 clear-site 命令行
@click.command('clear-site')
@with_appcontext
def clear_site_command():
    sql = "delete from siteTb where id not in (select sid from userTb) and id > 3;"
    db.goSQL(sql)
    # 输出日志
    click.echo('Clear the site success.')